﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using SystemDirectory.Entity;

namespace SystemDirectory.DAO
{
    public class DAOContact
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString2"].ToString());

        public DataTable Get(string FirstName, string SurName, bool ShowInactive)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand com;
            if (ShowInactive)
            {
                com = new SqlCommand("SELECT * FROM Contact WHERE FirstName LIKE @FirstName AND SurName LIKE @SurName", con);
            }
            else
            {
                com = new SqlCommand("SELECT * FROM Contact WHERE FirstName LIKE @FirstName AND SurName LIKE @SurName AND IsActive = @IsActive", con);
                com.Parameters.Add("@IsActive", SqlDbType.Bit);
                com.Parameters["@IsActive"].Value = ShowInactive;
            }
            com.Parameters.Add("@FirstName", SqlDbType.NVarChar, 255);
            com.Parameters["@FirstName"].Value = "%" + FirstName + "%";
            com.Parameters.Add("@SurName", SqlDbType.NVarChar, 255);
            com.Parameters["@SurName"].Value = "%" + FirstName + "%";
            SqlDataAdapter adapt = new SqlDataAdapter(com);
            DataTable dtResult = new DataTable();
            adapt.Fill(dtResult);
            return dtResult;
        }

        public bool Add(Contact ContactIn)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            SqlCommand com = new SqlCommand("INSERT INTO Contact VALUES (@ManId, @First, @Sur, @Know, @Office, @Mobile, @STHome, @Email, @ConId, @JRole, @Work, @JTitle, @IsActive)", con);
            com.Parameters.Add("@ManId", SqlDbType.Int);
            com.Parameters["@ManId"].Value = ContactIn.ManageId;
            com.Parameters.Add("@First", SqlDbType.NVarChar, 255);
            com.Parameters["@First"].Value = ContactIn.FirstName;
            com.Parameters.Add("@Sur", SqlDbType.NVarChar, 255);
            com.Parameters["@Sur"].Value = ContactIn.SurName;
            com.Parameters.Add("@Know", SqlDbType.NVarChar, 255);
            com.Parameters["@Know"].Value = ContactIn.KnownAs;
            com.Parameters.Add("@Office", SqlDbType.NVarChar, 255);
            com.Parameters["@Office"].Value = ContactIn.OfficePhone;
            com.Parameters.Add("@Mobile", SqlDbType.NVarChar, 255);
            com.Parameters["@Mobile"].Value = ContactIn.MobilePhone;
            com.Parameters.Add("@STHome", SqlDbType.NVarChar, 255);
            com.Parameters["@STHome"].Value = ContactIn.STHomePhone;
            com.Parameters.Add("@Email", SqlDbType.NVarChar, 255);
            com.Parameters["@Email"].Value = ContactIn.Email;
            com.Parameters.Add("@ConId", SqlDbType.Int);
            com.Parameters["@ConId"].Value = ContactIn.ContactId;
            com.Parameters.Add("@JRole", SqlDbType.NVarChar, 255);
            com.Parameters["@JRole"].Value = ContactIn.JobRole;
            com.Parameters.Add("@Work", SqlDbType.NVarChar, 255);
            com.Parameters["@Work"].Value = ContactIn.WorkBase;
            com.Parameters.Add("@JTitle", SqlDbType.NVarChar, 255);
            com.Parameters["@JTitle"].Value = ContactIn.JobTitle;
            com.Parameters.Add("@IsActive", SqlDbType.Bit);
            com.Parameters["@IsActive"].Value = ContactIn.IsActive;

            return Convert.ToBoolean(com.ExecuteNonQuery());
        }

        public Contact GetById(int id)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand com = new SqlCommand("SELECT * FROM Contact WHERE ContactID = @id", con);
            com.Parameters.Add("@id", SqlDbType.Int);
            com.Parameters["@id"].Value = id;
            SqlDataAdapter adapt = new SqlDataAdapter(com);
            DataTable dtResult = new DataTable();
            adapt.Fill(dtResult);
            Contact contact = new Contact();
            contact.ContactId = Convert.ToInt32(dtResult.Rows[0]["ContactID"].ToString());
            contact.ManageId = Convert.ToInt32(dtResult.Rows[0]["ManagerID"].ToString());
            contact.FirstName = dtResult.Rows[0]["Firstname"].ToString();
            contact.SurName = dtResult.Rows[0]["Surname"].ToString();
            contact.KnownAs = dtResult.Rows[0]["KnowAs"].ToString();
            contact.OfficePhone = dtResult.Rows[0]["OfficePhone"].ToString();
            contact.MobilePhone = dtResult.Rows[0]["MobilePhone"].ToString();
            contact.STHomePhone = dtResult.Rows[0]["STHomePhone"].ToString();
            contact.Email = dtResult.Rows[0]["Email"].ToString();
            contact.ContactType = Convert.ToInt32(dtResult.Rows[0]["ContactTypeID"].ToString());
            contact.JobRole = dtResult.Rows[0]["JobRole"].ToString();
            contact.WorkBase = dtResult.Rows[0]["WorkBase"].ToString();
            contact.JobTitle = dtResult.Rows[0]["JobTitle"].ToString();
            contact.IsActive = Convert.ToBoolean(dtResult.Rows[0]["IsActive"].ToString());
            return contact;
        }

        public bool Update(Contact ContactIn)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            SqlCommand com = new SqlCommand("UPDATE Contact SET ManagerID=@ManId, Firstname=@First, Surname=@Sur, KnowAs=@Know, OfficePhone=@Office, MobilePhone=@Mobile, STHomePhone=@STHome, Email=@Email, ContactTypeID=@ConId, JobRole=@JRole, WorkBase=@Work, JobTitle=@JTitle, IsActive=@IsActive WHERE ContactID=@id", con);
            com.Parameters.Add("@Id", SqlDbType.Int);
            com.Parameters["@Id"].Value = ContactIn.ContactId;
            com.Parameters.Add("@ManId", SqlDbType.Int);
            com.Parameters["@ManId"].Value = ContactIn.ManageId;
            com.Parameters.Add("@First", SqlDbType.NVarChar, 255);
            com.Parameters["@First"].Value = ContactIn.FirstName;
            com.Parameters.Add("@Sur", SqlDbType.NVarChar, 255);
            com.Parameters["@Sur"].Value = ContactIn.SurName;
            com.Parameters.Add("@Know", SqlDbType.NVarChar, 255);
            com.Parameters["@Know"].Value = ContactIn.KnownAs;
            com.Parameters.Add("@Office", SqlDbType.NVarChar, 255);
            com.Parameters["@Office"].Value = ContactIn.OfficePhone;
            com.Parameters.Add("@Mobile", SqlDbType.NVarChar, 255);
            com.Parameters["@Mobile"].Value = ContactIn.MobilePhone;
            com.Parameters.Add("@STHome", SqlDbType.NVarChar, 255);
            com.Parameters["@STHome"].Value = ContactIn.STHomePhone;
            com.Parameters.Add("@Email", SqlDbType.NVarChar, 255);
            com.Parameters["@Email"].Value = ContactIn.Email;
            com.Parameters.Add("@ConId", SqlDbType.Int);
            com.Parameters["@ConId"].Value = ContactIn.ContactId;
            com.Parameters.Add("@JRole", SqlDbType.NVarChar, 255);
            com.Parameters["@JRole"].Value = ContactIn.JobRole;
            com.Parameters.Add("@Work", SqlDbType.NVarChar, 255);
            com.Parameters["@Work"].Value = ContactIn.WorkBase;
            com.Parameters.Add("@JTitle", SqlDbType.NVarChar, 255);
            com.Parameters["@JTitle"].Value = ContactIn.JobTitle;
            com.Parameters.Add("@IsActive", SqlDbType.Bit);
            com.Parameters["@IsActive"].Value = ContactIn.IsActive;

            return Convert.ToBoolean(com.ExecuteNonQuery());
        }
    }
}